package ca.ubc.cs304.library.actions;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ca.ubc.cs304.library.gui.LibWindow;
import ca.ubc.cs304.library.sql.OracleDatabaseWrapper;

public class ClerkActions {
	private static final String ADD_BORROWER_SQL = "INSERT INTO Borrower (bid, password, name, address, phone, EmailAddress, SINOrStNo, ExpiryDate, type) VALUES (seqBid.nextVal,?,?,?,?,?,?,?,?)";
	private static final String CHECK_OUT_BORROWING_SQL = "INSERT INTO Borrowing(BorID, BId, callNumber, copyNo, outDate, inDate) VALUES (seqBorId.nextval, ?, ?, ?, current_date, null)";
	private static final String CHANGE_STATUS_BOOKCOPY_SQL = "UPDATE BookCopy SET Status = ? WHERE CallNumber = ? AND CopyNo = ?";
	private static final String FIND_BORROWER_TYPE_SQL = "SELECT Type FROM Borrower WHERE Bid = ?";
	private static final String FIND_BORROWER_BOOK_TIME_LIMIT_SQL = "SELECT bookTimeLimit FROM BorrowerType WHERE Type = ?";
	private static final String CHECK_IN_BORROWING_SQL = "UPDATE Borrowing SET inDate = current_date WHERE borId = ?";
	private static final String GET_MAX_BORID_FROM_BORROWING_SQL = "SELECT MAX(BorID) FROM Borrowing WHERE callNumber = ? AND copyNo = ? GROUP BY callNumber";
	private static final String CHECK_CALL_NUM_EXISTANCE_SQL = "SELECT count(*) FROM book WHERE callNumber = ?";
	private static final String CHECK_BOOK_STATUS_SQL = "SELECT Status FROM BookCopy WHERE callNumber = ? AND copyNo = ?";
	private static final String FIND_EARLIEST_HOLD_SQL = "SELECT MIN(hid) FROM (SELECT hid FROM holdrequest WHERE callNumber = ? AND issuedDate IS NULL)";
	private static final String FIND_BID_FROM_HOLD_SQL = "SELECT bid FROM HoldRequest WHERE hid = ?";
	private static final String FIND_EMAIL_FROM_BID_SQL = "SELECT emailAddress FROM borrower WHERE bid = ?";
	private static final String SET_HOLD_ISSUED_DATE_SQL = "UPDATE HoldRequest SET IssuedDate = current_date WHERE hid = ?";
	private static final String CHECK_OVERDUE_SQL = "SELECT name, emailAddress, callNumber, borID FROM (borrower E INNER JOIN borrowing B on E.bid = B.bid) INNER JOIN borrowerType D on E.type = D.type WHERE (B.outDate + D.bookTimeLimit < current_date) AND B.inDate IS NULL";
	private static final String CHECK_FOR_FINES_SQL = "SELECT name FROM (borrowing B INNER JOIN borrower E on B.bid = E.bid) INNER JOIN borrowerType D on D.type = E.type WHERE B.borid = ? AND (B.outdate + d.bookTimelimit < current_date)";	
	private static final String FIND_MAX_BID_SQL = "SELECT max(bid) FROM borrower";
	private static final String CHECK_BID_EXISTANCE_SQL = "SELECT count(*) from borrower where bid = ?";
	private static final String ADD_FINE_SQL = "INSERT INTO Fine values (seqFid.nextval, 10, sysdate, null, (?))";
	
	private ClerkActionsDelegate delegate;

	public ClerkActions() {
		delegate = new ClerkActionsDelegate();
	}

	public void addBorrower(String sinOrStNo, String name, String password, String address, String phoneNumber, String emailAddress, Date expiryDate, String type) throws SQLException{
		OracleDatabaseWrapper dbWrapper= OracleDatabaseWrapper.getInstance();
		int bid = 0;
		
		if (dbWrapper.isConnected()) {
				PreparedStatement pStatement = dbWrapper.createPreparedStatement(ADD_BORROWER_SQL);
				pStatement.setString(1, password);
				pStatement.setString(2, name);
				pStatement.setString(3, address);
				pStatement.setString(4, phoneNumber);
				pStatement.setString(5, emailAddress);
				pStatement.setString(6, sinOrStNo);
				pStatement.setDate(7, expiryDate);
				pStatement.setString(8, type);

				pStatement.execute();
				pStatement.close();
				
				PreparedStatement pStatement1 = dbWrapper.createPreparedStatement(FIND_MAX_BID_SQL);
				ResultSet bidSet = pStatement1.executeQuery();
				
				if(bidSet.next()) {
					bid = bidSet.getInt(1);
				}
				
				pStatement1.close();
				
				delegate.addBorrowerDidFinish(bid);
			
		} else {
			delegate.addBorrowerDidReceiveDatabaseIsNotConnected();
		}
	}

	public void checkOut(String bid, ArrayList<String[]> callNumBook) throws SQLException{
		OracleDatabaseWrapper dbWrapper= OracleDatabaseWrapper.getInstance();
		int bookTimeLimit = -1;
		int bidExistOrNot = 0;
		int intFormBid = 0;
		String borrowerType = "";
		boolean allExecutedSuccessfully = true;
		boolean successfullyExecuted = true;
		boolean validCallNum = true;
		boolean allValidCallNum = true;
		boolean statusOfBook = false;
		boolean statusOfAllBooks = false;
		boolean errorPopup = false;
		String callNum = "";
		int copyNum = 0;

		if (dbWrapper.isConnected()) {
			//finds borrower type
			intFormBid = Integer.parseInt(bid);
			borrowerType = findBorrowerType(dbWrapper, intFormBid);
			
			try {
				// check if borrower id exists in the system
				PreparedStatement pStatement0 = dbWrapper.createPreparedStatement(CHECK_BID_EXISTANCE_SQL);
				pStatement0.setInt(1, intFormBid);
				ResultSet bidExistanceSet = pStatement0.executeQuery();
			
				if(bidExistanceSet.next()) {
					bidExistOrNot = bidExistanceSet.getInt(1);
				}
			
				if(bidExistOrNot == 0) {
					delegate.bidNotInSystem(bid);
					errorPopup = true;
				}
			} catch (SQLException e) {
				// call pop-up if we don't find the borrower type for the given bid in the borrowing table
					delegate.borrowerNotInSystem();
					errorPopup = true;
			}

			// finds the time limit for the given borrower
			bookTimeLimit = findBookTimeLimit(dbWrapper, borrowerType);

			// call pop-up if we don't find the type of borrower in the borrowerType table
			if(bookTimeLimit == -1 && !errorPopup) {
				delegate.borrowerTypeDoesNotExist();
				errorPopup = true;
			}
			
			// checks to see if one of the call numbers entered is registered to a book that is already signed out
			for(int j = 0; j < callNumBook.size(); j++) {
				statusOfBook = hasBookBeenCheckedOut(dbWrapper, callNum, copyNum);

				if(statusOfBook) {
					statusOfAllBooks = true;
				}
			}
			
			for(int i = 0; i < callNumBook.size(); i++) {

				if(callNumBook.get(i) != null) {
					callNum = (callNumBook.get(i))[LibWindow.CALL_NUMBER_INDEX];
					copyNum = Integer.parseInt((callNumBook.get(i))[LibWindow.COPY_NUMBER_INDEX]);
					validCallNum = isCallNumExist(dbWrapper, callNum);

					if(validCallNum && !statusOfAllBooks) {
						successfullyExecuted = addCheckOutToTables(dbWrapper, callNum, copyNum, bid, bookTimeLimit);
					} else {
						if(!validCallNum) {
							allValidCallNum = false;
						}
					}

					if (!successfullyExecuted) {
						allExecutedSuccessfully = false;
					}
				}
			}

			if(statusOfAllBooks) {
				delegate.bookAlreadyOut();
				errorPopup = true;
			}

			// makes sure pop-up only appears once even though multiple copy numbers are wrong
			if(!allValidCallNum) {
				delegate.callNumberDoesNotExist();
				errorPopup = true;
			}

			// makes sure pop-up only appears once for all call numbers entered, not once per successful insertion
			if (allExecutedSuccessfully && !statusOfAllBooks && !errorPopup) {
				delegate.checkOutDidFinish();
			}
		} else {
			delegate.checkOutDidReceiveDatabaseIsNotConnected();
		}
	}

	
	private boolean hasBookBeenCheckedOut(OracleDatabaseWrapper dbWrapper, String callNum, int copyNum) throws SQLException{
		boolean checkedOut = false;

		PreparedStatement pStatement = dbWrapper.createPreparedStatement(CHECK_BOOK_STATUS_SQL);
		pStatement.setString(1, callNum);
		pStatement.setInt(2, copyNum);
		ResultSet statusOfBook = pStatement.executeQuery();

		if(statusOfBook.next()) {
			String status = statusOfBook.getString(1);

			if(status.compareTo("Out") == 0) {
				checkedOut = true;
			}
		}

		pStatement.close();

		return checkedOut;
	}


	private boolean addCheckOutToTables(OracleDatabaseWrapper dbWrapper, String callNum, int copyNum, String bid, int bookTimeLimit) throws SQLException {
		boolean hasExecuted = false;

		// updates book status in book copy table
		changeBookCopyStatus(dbWrapper, callNum, copyNum, "Out");

		PreparedStatement pStatement = dbWrapper.createPreparedStatement(CHECK_OUT_BORROWING_SQL);
		pStatement.setInt(1, Integer.parseInt(bid));
		pStatement.setString(2, callNum);
		pStatement.setInt(3, copyNum);
		pStatement.execute();
		pStatement.close();

		hasExecuted = true;

		return hasExecuted;
	}

	private boolean isCallNumExist(OracleDatabaseWrapper dbWrapper, String callNum) throws SQLException {
		boolean hasExistance = false;

		PreparedStatement pStatement = dbWrapper.createPreparedStatement(CHECK_CALL_NUM_EXISTANCE_SQL);
		pStatement.setString(1, callNum);
		ResultSet doesCallNumExist = pStatement.executeQuery();

		if(doesCallNumExist.next()) {
			if(doesCallNumExist.getInt(1) >= 1) {
				hasExistance = true;
			}
		}

		return hasExistance;
	}

	public void processReturn(String callNum, int copyNum) throws SQLException {
		OracleDatabaseWrapper dbWrapper= OracleDatabaseWrapper.getInstance();
		int holdRequesterBid = 0;
		int holdId = 0;
		int borID = 0;
		String holdRequesterEmail = "";
		boolean overdue = false;
		// designed so that the success popup only appears if no error popups were called
		boolean errorPopupShown = false;
		
		if(dbWrapper.isConnected()) {
			if(isCallNumExist(dbWrapper, callNum)) {
				boolean bookActuallyOut = hasBookBeenCheckedOut(dbWrapper, callNum, copyNum);
				if(bookActuallyOut) {
					// check to see if there is a valid hold
					PreparedStatement pStatement = dbWrapper.createPreparedStatement(FIND_EARLIEST_HOLD_SQL);
					pStatement.setString(1, callNum);
					ResultSet doesHoldExist = pStatement.executeQuery();

					if(doesHoldExist.next()) {
						holdId = doesHoldExist.getInt(1);
					} 

					pStatement.close();

					// get bid from hid
					PreparedStatement pStatement1 = dbWrapper.createPreparedStatement(FIND_BID_FROM_HOLD_SQL);
					pStatement1.setInt(1, holdId);
					ResultSet holdRequesterBidSet = pStatement1.executeQuery();

					if(holdRequesterBidSet.next()) {
						holdRequesterBid = holdRequesterBidSet.getInt(1);
					} 

					pStatement1.close();
					
					// get email from bid
					PreparedStatement pStatement2 = dbWrapper.createPreparedStatement(FIND_EMAIL_FROM_BID_SQL);
					pStatement2.setInt(1, holdRequesterBid);
					ResultSet emailAddressSet = pStatement2.executeQuery();

					if(emailAddressSet.next()) {
						holdRequesterEmail = emailAddressSet.getString(1);
					} 

					pStatement2.close();

					if(holdRequesterBid != 0 && holdRequesterEmail.compareTo("") != 0) {
						// changes book status to on hold
						changeBookCopyStatus(dbWrapper, callNum, copyNum, "On-hold");

						// sets issuedDate in holds table to show that the hold request was fulfilled
						PreparedStatement pStatement3 = dbWrapper.createPreparedStatement(SET_HOLD_ISSUED_DATE_SQL);
						pStatement3.setInt(1, holdId);
						pStatement3.execute();
						pStatement3.close();

						delegate.emailBidOnHold(holdRequesterBid, holdRequesterEmail);
					} else {
						// change book copy status to in if there is no hold
						changeBookCopyStatus(dbWrapper, callNum, copyNum, "In");
					}

					// get max borId from borrowing table since that would be the most recent check-out
					borID = findMaxBorID(dbWrapper, callNum, copyNum);
					
					// supposed to set the inDate of the tuple with borId to the current date
					PreparedStatement pStatement5 = dbWrapper.createPreparedStatement(CHECK_IN_BORROWING_SQL);
					pStatement5.setInt(1, borID);
					pStatement5.execute();
					pStatement5.close();
					
					// checks to see if something is overdue.. if it is, overdue will be true
					overdue = hasFine(dbWrapper, borID);

					if(overdue) {
						PreparedStatement pStatement6 = dbWrapper.createPreparedStatement(ADD_FINE_SQL);
						pStatement6.setInt(1, borID);
						pStatement6.execute();
						delegate.bookOverdue();
					}

					if (!errorPopupShown) {
						delegate.processReturnSuccessfully();
					}
				} else {
					delegate.bookNotOut();
					errorPopupShown = true;
				}
			} else {
				delegate.callNumberDoesNotExist();
				errorPopupShown = true;
			}
		} else {
			delegate.processReturnDidReceiveDatabaseIsNotConnected();
		}
	}

	private boolean hasFine(OracleDatabaseWrapper dbWrapper, int borID) throws SQLException {
		boolean hasFine = true;
		String nameOfPersonWhoOwesFine = "";

		PreparedStatement pStatement = dbWrapper.createPreparedStatement(CHECK_FOR_FINES_SQL);
		pStatement.setInt(1, borID);
		ResultSet fineSet = pStatement.executeQuery();

		while(fineSet.next()) {
			nameOfPersonWhoOwesFine = fineSet.getString(1);
		}

		pStatement.close();

		if(nameOfPersonWhoOwesFine.compareTo("") == 0) {
			hasFine = false;
		}

		return hasFine;

	}

	private int findMaxBorID(OracleDatabaseWrapper dbWrapper, String callNum, int copyNum) throws SQLException {
		int borId = 0;

		PreparedStatement pStatement = dbWrapper.createPreparedStatement(GET_MAX_BORID_FROM_BORROWING_SQL);
		pStatement.setString(1, callNum);
		pStatement.setInt(2, copyNum);
		ResultSet borIDSet = pStatement.executeQuery();

		if(borIDSet.next()) {
			borId = borIDSet.getInt(1);
		}

		pStatement.close();
		
		return borId;
	}

	private void changeBookCopyStatus (OracleDatabaseWrapper dbWrapper, String callNum, int copyNum, String status) throws SQLException {
		PreparedStatement pStatement = dbWrapper.createPreparedStatement(CHANGE_STATUS_BOOKCOPY_SQL);
		pStatement.setString(1, status);
		pStatement.setString(2, callNum);
		pStatement.setInt(3, copyNum);
		pStatement.execute();
		pStatement.close();
	}

	private String findBorrowerType(OracleDatabaseWrapper dbWrapper, int bid) throws SQLException {
		String borrowerType = "";
		PreparedStatement pStatement = dbWrapper.createPreparedStatement(FIND_BORROWER_TYPE_SQL);
		pStatement.setInt(1, bid);
		ResultSet borrowerTypeResultSet = pStatement.executeQuery();

		if(borrowerTypeResultSet.next()) {
			borrowerType = borrowerTypeResultSet.getString(1);
		}

		pStatement.close();
	
		return borrowerType;
	}

	private int findBookTimeLimit(OracleDatabaseWrapper dbWrapper, String type) throws SQLException{
		int bookTimeLimit = -1;

		PreparedStatement pStatement1 = dbWrapper.createPreparedStatement(FIND_BORROWER_BOOK_TIME_LIMIT_SQL);
		pStatement1.setString(1, type);
		ResultSet bookTimeLimitSet = pStatement1.executeQuery();

		if(bookTimeLimitSet.next()) {
			bookTimeLimit = bookTimeLimitSet.getInt("BookTimeLimit");
		}

		pStatement1.close();
		
		return bookTimeLimit;
	}

	public ResultSet checkOverdue() throws SQLException {
		ResultSet overdueBooksSet = null;
		OracleDatabaseWrapper dbWrapper= OracleDatabaseWrapper.getInstance();

		if(dbWrapper.isConnected()) {
			PreparedStatement pStatement = dbWrapper.createPreparedStatement(CHECK_OVERDUE_SQL);
			overdueBooksSet = pStatement.executeQuery();

		} else {
			delegate.checkOverdueDidReceiveDatabaseIsNotConnected();
		}

		return overdueBooksSet;
	}

}
